
//
//  DataBaseHandle.m
//  趣·美食
//
//  Created by dllo on 15/9/30.
//  Copyright (c) 2015年 金旭. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>

@implementation DataBaseHandle

+ (instancetype)shareDataBaseHandle {
    
    /* 创建一个单例 */
    static DataBaseHandle *dataBaseHandle = nil;
    if (dataBaseHandle == nil) {
        dataBaseHandle = [[DataBaseHandle alloc] init];
    }
    return dataBaseHandle;
}

/* 创建一个数据库对象 */
static sqlite3 *db;

- (void)openDB {
    
//    NSLog(@"%@", NSHomeDirectory());
    
    /* 数据库文件路径 */
    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"favoriteFoods.sqlite"];
    
    if (db != nil) {
//        NSLog(@"数据库已经打开");
        return;
    }
    
    /* 将数据库和数据库对象关联 打开数据库*/
    int result = sqlite3_open(dbPath.UTF8String, &db);
    
    if (SQLITE_OK == result) {
//        NSLog(@"数据库打开成功");
    } else {
//        NSLog(@"数据库打开失败");
    }
}

/* 创建表格 */
- (void)creatTable {
    
    /* 创建一个食物详情表格 */
    NSString *creatSQl = @"CREATE TABLE IF NOT EXISTS FoodDetails (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, code TEXT, health_light NSINTEGER, thumb_image_url TEXT, calory TEXT, weight TEXT)";
    
    int result = sqlite3_exec(db, creatSQl.UTF8String, NULL, NULL, NULL);
    
    /* 返回相应信息 */
    if (SQLITE_OK == result) {
//        NSLog(@"表格创建成功");
    } else {
        
//        NSLog(@"表格创建失败");
//        NSLog(@"错误码: %d", result);

    }
    
}

#pragma mark _ 插入详情信息(收藏)
- (void)insertFoodDetails:(GFDetialsModel *)deModel {
    
    /* 创建语句 */
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO FoodDetails(name, code, health_light, thumb_image_url, calory, weight) VALUES('%@', '%@', '%ld', '%@', '%@', '%@')", deModel.name, deModel.code, deModel.health_light, deModel.thumb_image_url, deModel.calory, deModel.weight];
    
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == result) {
//        NSLog(@"收藏/插入成功");
    } else {
//        NSLog(@"插入失败");
//        NSLog(@"错误码: %d", result);
    }
    
}

#pragma mark _ 删除详情信息
- (void)deleteFoodDetailsWithFoodName:(NSString *)name {
    
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM FoodDetails WHERE code = '%@'", name];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == result) {
//        NSLog(@"数据删除成功");
    } else {
//        NSLog(@"数据删除失败");
//        NSLog(@"错误码: %d", result);
    }
    
}

#pragma mark _ 查询详情信息
- (NSArray *)selectFoodDetailsWithFoodName:(NSString *)name {
    
    /* 建立一个数组, 存放符合条件的数据 */
    NSMutableArray *arr = [NSMutableArray array];
    
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT *FROM FoodDetails WHERE code = '%@'", name];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == result) {
//        NSLog(@"查询开始");
        
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *code = sqlite3_column_text(stmt, 2);
            NSInteger health_light = sqlite3_column_int64(stmt, 3);
            const unsigned char *thumb_image_url = sqlite3_column_text(stmt, 4);
            const unsigned char *calory = sqlite3_column_text(stmt, 5);
            const unsigned char *weight = sqlite3_column_text(stmt, 6);
            
            GFDetialsModel *deModel = [[GFDetialsModel alloc] init];
            
            deModel.name = [NSString stringWithUTF8String:(const char *)name];
            deModel.code = [NSString stringWithUTF8String:(const char *)code];
            deModel.health_light = health_light;
            deModel.thumb_image_url = [NSString stringWithUTF8String:(const char *)thumb_image_url];
            deModel.calory = [NSString stringWithUTF8String:(const char *)calory];
            deModel.weight = [NSString stringWithUTF8String:(const char *)weight];
            
            [arr addObject:deModel];

            
        }
        
        sqlite3_finalize(stmt);
    } else {
        
        sqlite3_finalize(stmt);
//        NSLog(@"查询失败");
    }
    return arr;
}

#pragma mark _ 查询所有信息
- (NSArray *)selectAllFoodDetails {
    
    NSMutableArray *arr = [NSMutableArray array];
    
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT *FROM FoodDetails"];
                           
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == result) {
//        NSLog(@"开始查询");
        
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *code = sqlite3_column_text(stmt, 2);
            NSInteger health_light = sqlite3_column_int64(stmt, 3);
            const unsigned char *thumb_image_url = sqlite3_column_text(stmt, 4);
            const unsigned char *calory = sqlite3_column_text(stmt, 5);
            const unsigned char *weight = sqlite3_column_text(stmt, 6);
            
            GFDetialsModel *deModel = [[GFDetialsModel alloc] init];
            
            deModel.name = [NSString stringWithUTF8String:(const char *)name];
            deModel.code = [NSString stringWithUTF8String:(const char *)code];
            deModel.health_light = health_light;
            deModel.thumb_image_url = [NSString stringWithUTF8String:(const char *)thumb_image_url];
            deModel.calory = [NSString stringWithUTF8String:(const char *)calory];
            deModel.weight = [NSString stringWithUTF8String:(const char *)weight];
            
            [arr addObject:deModel];

            
        }
        
        sqlite3_finalize(stmt);
    } else {
//        NSLog(@"查询失败");
        sqlite3_finalize(stmt);
    }
    return arr;
}

#pragma mark _ 删除表格
- (void)dropTable {
    
    NSString *dropSQL = @"DROP TABLE IF EXISTS FoodDetails";
    
    int result = sqlite3_exec(db, dropSQL.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == result) {
//        NSLog(@"表格删除成功");
    } else {
//        NSLog(@"表格删除失败");
//        NSLog(@"错误码: %d", result);
    }
    
}



@end
